package xju.rjxy.shop.mapper.impl;

import xju.rjxy.shop.mapper.UserMapper;
import xju.rjxy.shop.model.domain.User;
import xju.rjxy.shop.model.vo.UserVO;
import xju.rjxy.shop.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 用户数据访问接口实现类
 */
public class UserMapperImpl implements UserMapper {
    private final QueryRunner runner = new QueryRunner();

    /**
     * 查询用户列表
     */
    @Override
    public List<UserVO> selectUserList() {
        // 重写后的SQL语句，使用正确的表名和字段
        String sql = "SELECT " +
                "id, " +
                "username, " +
                "email, " +
                "gender, " +
                "avatar, " +
                "account_balance AS accountBalance, " +
                "create_time AS createTime, " +
                "flag, " +
                "accout_points AS accountPoints " +
                "FROM users " +
                "WHERE deleted = 0"; // 添加删除标记过滤

        List<UserVO> userList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            userList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(UserVO.class)
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return userList;
    }
    
    /**
     * 根据用户名查询用户
     */
    @Override
    public User selectByUsername(String username) {
        String sql = "SELECT * FROM users WHERE username = ? AND deleted = 0";
        User user = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            user = runner.query(connection, sql, new BeanHandler<>(User.class), username);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return user;
    }
    
    /**
     * 根据手机号查询用户
     */
    @Override
    public User selectByPhoneNumber(String phoneNumber) {
        String sql = "SELECT id, username, phone AS phoneNumber, password, email, gender, avatar, account_balance AS accountBalance, account_points AS accountPoints, create_time AS createTime, flag, activate_code AS activateCode, deleted FROM users WHERE phone = ? AND deleted = 0";
        User user = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            user = runner.query(connection, sql, new BeanHandler<>(User.class), phoneNumber);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return user;
    }
    
    /**
     * 插入用户
     */
    @Override
    public int insertUser(User user) {
        String sql = "INSERT INTO users (username, phone, password, email, gender, avatar, " +
                "account_balance, account_points, create_time, flag, activate_code, deleted) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        int result = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            result = runner.update(connection, sql, 
                    user.getUsername(), 
                    user.getPhoneNumber(), 
                    user.getPassword(), 
                    user.getEmail(), 
                    user.getGender(), 
                    user.getAvatar(), 
                    user.getAccountBalance(), 
                    user.getAccountPoints(), 
                    user.getCreateTime(), 
                    user.getFlag(), 
                    user.getActivateCode(), 
                    user.getDeleted());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return result;
    }
    
    /**
     * 根据ID查询用户
     */
    @Override
    public User selectById(Integer id) {
        String sql = "SELECT id, username, phone AS phoneNumber, password, email, gender, avatar, " +
                     "account_balance AS accountBalance, account_points AS accountPoints, " +
                     "create_time AS createTime, flag, activate_code AS activateCode, deleted " +
                     "FROM users WHERE id = ? AND deleted = 0";
        User user = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            user = runner.query(connection, sql, new BeanHandler<>(User.class), id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return user;
    }
    
    /**
     * 更新用户信息
     */
    @Override
    public int updateUser(User user) {
        System.out.println("=== UserMapper.updateUser 调试信息 ===");
        System.out.println("输入用户对象: " + user);
        
        // 构建动态SQL，只更新非null字段
        StringBuilder sqlBuilder = new StringBuilder("UPDATE users SET ");
        java.util.List<Object> params = new java.util.ArrayList<>();
        
        System.out.println("--- 构建SQL和参数 ---");
        
        if (user.getUsername() != null) {
            sqlBuilder.append("username = ?, ");
            params.add(user.getUsername());
            System.out.println("添加参数: username = " + user.getUsername());
        }
        if (user.getPhoneNumber() != null) {
            sqlBuilder.append("phone = ?, ");
            params.add(user.getPhoneNumber());
            System.out.println("添加参数: phone = " + user.getPhoneNumber());
        }
        if (user.getPassword() != null) {
            sqlBuilder.append("password = ?, ");
            params.add(user.getPassword());
            System.out.println("添加参数: password = ***");
        }
        if (user.getEmail() != null) {
            sqlBuilder.append("email = ?, ");
            params.add(user.getEmail());
            System.out.println("添加参数: email = " + user.getEmail());
        }
        if (user.getGender() != null) {
            sqlBuilder.append("gender = ?, ");
            params.add(user.getGender());
            System.out.println("添加参数: gender = " + user.getGender());
        }
        if (user.getAvatar() != null) {
            sqlBuilder.append("avatar = ?, ");
            params.add(user.getAvatar());
            System.out.println("添加参数: avatar = " + user.getAvatar());
        }
        if (user.getAccountBalance() != null) {
            sqlBuilder.append("account_balance = ?, ");
            params.add(user.getAccountBalance());
            System.out.println("添加参数: account_balance = " + user.getAccountBalance());
        }
        if (user.getAccountPoints() != null) {
            sqlBuilder.append("account_points = ?, ");
            params.add(user.getAccountPoints());
            System.out.println("添加参数: account_points = " + user.getAccountPoints());
        }
        
        // 移除最后一个逗号和空格
        if (params.size() > 0) {
            sqlBuilder.setLength(sqlBuilder.length() - 2);
        }
        
        sqlBuilder.append(" WHERE id = ?");
        params.add(user.getId());
        System.out.println("添加参数: id = " + user.getId());
        
        String sql = sqlBuilder.toString();
        System.out.println("最终SQL: " + sql);
        System.out.println("参数数量: " + params.size());
        System.out.println("参数列表: " + params);
        
        int result = 0;
        try {
            System.out.println("--- 执行数据库更新 ---");
            Connection connection = JDBCUtils.getConnection();
            System.out.println("数据库连接获取成功");
            
            result = runner.update(connection, sql, params.toArray());
            System.out.println("数据库更新执行完成，影响行数: " + result);
            
        } catch (SQLException e) {
            System.out.println("数据库更新异常: " + e.getMessage());
            System.out.println("SQL状态: " + e.getSQLState());
            System.out.println("错误代码: " + e.getErrorCode());
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
            System.out.println("数据库连接已关闭");
        }
        
        System.out.println("Mapper更新方法返回: " + result);
        return result;
    }

    /**
     * 根据用户名或手机号查找用户
     */
    @Override
    public User selectByUsernameOrPhone(String keyword) {
        String sql = "SELECT id, username, phone AS phoneNumber, password, email, gender, avatar, account_balance AS accountBalance, account_points AS accountPoints, create_time AS createTime, flag, activate_code AS activateCode, deleted FROM users WHERE (username = ? OR phone = ?) AND deleted = 0";
        User user = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            user = runner.query(connection, sql, new BeanHandler<>(User.class), keyword, keyword);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return user;
    }
}
